import sys
import os

# 添加项目根目录到系统路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from database import Database

class FeeQuery:
    def __init__(self):
        self.db = Database()
    
    def query_dormitory_fees(self, dormitory_id, payment_status=None):
        """
        查询指定宿舍的费用记录
        
        参数:
            dormitory_id (int): 宿舍ID
            payment_status (str, optional): 支付状态筛选（unpaid/paid/all）
        
        返回:
            list: 费用记录列表
        """
        try:
            self.db.connect()
            
            # 检查宿舍是否存在
            self.db.cursor.execute(
                "SELECT building, room_number FROM dormitory WHERE id = ?",
                (dormitory_id,)
            )
            dormitory = self.db.cursor.fetchone()
            if not dormitory:
                print(f"错误：ID为 {dormitory_id} 的宿舍不存在")
                return [], None
            
            building, room_number = dormitory
            dormitory_info = f"{building}-{room_number}"
            
            # 构建查询SQL
            sql = "SELECT id, fee_type, amount, billing_date, due_date, payment_status, payment_date FROM fee WHERE dormitory_id = ?"
            params = [dormitory_id]
            
            # 根据支付状态筛选
            if payment_status and payment_status.lower() != 'all':
                sql += " AND payment_status = ?"
                params.append(payment_status.lower())
            
            sql += " ORDER BY billing_date DESC"
            
            # 执行查询
            self.db.cursor.execute(sql, params)
            fees = self.db.cursor.fetchall()
            
            return fees, dormitory_info
        except Exception as e:
            print(f"错误：查询宿舍费用时发生错误 - {str(e)}")
            return [], None
        finally:
            self.db.close()
    
    def query_fee_by_id(self, fee_id):
        """
        根据ID查询费用记录详情
        
        参数:
            fee_id (int): 费用记录ID
        
        返回:
            tuple: 费用记录详情
        """
        try:
            self.db.connect()
            
            # 查询费用记录
            self.db.cursor.execute(
                """SELECT f.id, f.fee_type, f.amount, f.billing_date, f.due_date, 
                f.payment_status, f.payment_date, d.building, d.room_number 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.id = ?""",
                (fee_id,)
            )
            fee = self.db.cursor.fetchone()
            
            if not fee:
                print(f"错误：ID为 {fee_id} 的费用记录不存在")
            
            return fee
        except Exception as e:
            print(f"错误：查询费用记录时发生错误 - {str(e)}")
            return None
        finally:
            self.db.close()
    
    def query_unpaid_fees(self, limit=50):
        """
        查询所有未缴费的费用记录
        
        参数:
            limit (int): 限制返回的记录数量
        
        返回:
            list: 未缴费的费用记录列表
        """
        try:
            self.db.connect()
            
            # 查询未缴费记录
            self.db.cursor.execute(
                """SELECT f.id, f.fee_type, f.amount, f.billing_date, f.due_date, 
                d.building, d.room_number 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.payment_status = 'unpaid' 
                ORDER BY f.due_date ASC 
                LIMIT ?""",
                (limit,)
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询未缴费记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_overdue_fees(self):
        """
        查询所有已逾期未缴费的费用记录
        
        返回:
            list: 已逾期未缴费的费用记录列表
        """
        try:
            self.db.connect()
            
            # 获取当前日期
            import datetime
            today = datetime.datetime.now().strftime("%Y-%m-%d")
            
            # 查询已逾期未缴费记录
            self.db.cursor.execute(
                """SELECT f.id, f.fee_type, f.amount, f.billing_date, f.due_date, 
                d.building, d.room_number 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.payment_status = 'unpaid' AND f.due_date < ? 
                ORDER BY f.due_date ASC""",
                (today,)
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询逾期费用时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_all_fees(self):
        """
        查询所有费用记录
        
        返回:
            list: 所有费用记录列表
        """
        try:
            self.db.connect()
            
            # 查询所有费用记录
            self.db.cursor.execute(
                """SELECT f.id, d.building, d.room_number, f.fee_type, f.amount, 
                f.billing_date, f.due_date, f.payment_status 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                ORDER BY f.billing_date DESC"""
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询所有费用记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_fees_by_dormitory(self, dormitory_id):
        """
        根据宿舍ID查询费用记录
        
        参数:
            dormitory_id (int): 宿舍ID
        
        返回:
            list: 指定宿舍的费用记录列表
        """
        try:
            self.db.connect()
            
            # 查询指定宿舍的费用记录
            self.db.cursor.execute(
                """SELECT f.id, d.building, d.room_number, f.fee_type, f.amount, 
                f.billing_date, f.due_date, f.payment_status 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.dormitory_id = ? 
                ORDER BY f.billing_date DESC""",
                (dormitory_id,)
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询宿舍费用记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_fees_by_status(self, status):
        """
        根据支付状态查询费用记录
        
        参数:
            status (str): 支付状态（unpaid/paid/overdue）
        
        返回:
            list: 指定状态的费用记录列表
        """
        try:
            self.db.connect()
            
            # 查询指定状态的费用记录
            self.db.cursor.execute(
                """SELECT f.id, d.building, d.room_number, f.fee_type, f.amount, 
                f.billing_date, f.due_date, f.payment_status 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.payment_status = ? 
                ORDER BY f.billing_date DESC""",
                (status,)
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询费用状态记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()
    
    def query_fees_by_type(self, fee_type):
        """
        根据费用类型查询费用记录
        
        参数:
            fee_type (str): 费用类型
        
        返回:
            list: 指定类型的费用记录列表
        """
        try:
            self.db.connect()
            
            # 查询指定类型的费用记录
            self.db.cursor.execute(
                """SELECT f.id, d.building, d.room_number, f.fee_type, f.amount, 
                f.billing_date, f.due_date, f.payment_status 
                FROM fee f 
                JOIN dormitory d ON f.dormitory_id = d.id 
                WHERE f.fee_type = ? 
                ORDER BY f.billing_date DESC""",
                (fee_type,)
            )
            fees = self.db.cursor.fetchall()
            
            return fees
        except Exception as e:
            print(f"错误：查询费用类型记录时发生错误 - {str(e)}")
            return []
        finally:
            self.db.close()

# 如果直接运行此文件，提供命令行界面
if __name__ == "__main__":
    query = FeeQuery()
    
    print("===== 宿舍费用查询 =====")
    print("1. 查询指定宿舍的费用记录")
    print("2. 查询指定费用记录详情")
    print("3. 查询所有未缴费记录")
    print("4. 查询所有已逾期未缴费记录")
    
    choice = input("请选择查询方式 (1-4): ")
    
    if choice == '1':
        try:
            dormitory_id = int(input("请输入宿舍ID: "))
            status_choice = input("请选择支付状态 (1-全部, 2-已缴费, 3-未缴费): ")
            
            status_map = {'1': 'all', '2': 'paid', '3': 'unpaid'}
            status = status_map.get(status_choice, 'all')
            
            fees, dormitory_info = query.query_dormitory_fees(dormitory_id, status)
            
            if fees and dormitory_info:
                print(f"\n宿舍 {dormitory_info} 的费用记录:")
                print("记录ID\t费用类型\t金额\t账单日期\t到期日期\t状态\t缴费日期")
                for fee in fees:
                    payment_date = fee[6] if fee[6] else "未缴费"
                    print(f"{fee[0]}\t{fee[1]}\t{fee[2]}\t{fee[3]}\t{fee[4]}\t{fee[5]}\t{payment_date}")
            elif dormitory_info:
                print(f"宿舍 {dormitory_info} 没有符合条件的费用记录")
        except ValueError:
            print("错误：宿舍ID必须是数字")
    
    elif choice == '2':
        try:
            fee_id = int(input("请输入费用记录ID: "))
            fee = query.query_fee_by_id(fee_id)
            
            if fee:
                print("\n费用记录详情:")
                print(f"记录ID: {fee[0]}")
                print(f"宿舍: {fee[7]}-{fee[8]}")
                print(f"费用类型: {fee[1]}")
                print(f"金额: {fee[2]}元")
                print(f"账单日期: {fee[3]}")
                print(f"到期日期: {fee[4]}")
                print(f"支付状态: {fee[5]}")
                print(f"缴费日期: {fee[6] if fee[6] else '未缴费'}")
        except ValueError:
            print("错误：费用记录ID必须是数字")
    
    elif choice == '3':
        fees = query.query_unpaid_fees()
        
        if fees:
            print("\n未缴费的费用记录:")
            print("记录ID\t费用类型\t金额\t账单日期\t到期日期\t宿舍")
            for fee in fees:
                print(f"{fee[0]}\t{fee[1]}\t{fee[2]}\t{fee[3]}\t{fee[4]}\t{fee[5]}-{fee[6]}")
        else:
            print("没有找到未缴费的费用记录")
    
    elif choice == '4':
        fees = query.query_overdue_fees()
        
        if fees:
            print("\n已逾期未缴费的费用记录:")
            print("记录ID\t费用类型\t金额\t账单日期\t到期日期\t宿舍")
            for fee in fees:
                print(f"{fee[0]}\t{fee[1]}\t{fee[2]}\t{fee[3]}\t{fee[4]}\t{fee[5]}-{fee[6]}")
        else:
            print("没有找到已逾期未缴费的费用记录")
    
    else:
        print("无效的选择")